﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using Bxas.Core.Exceptions;

namespace Bxas.Core
{
    public class ConnectionManager
    {
        private SqlConnection mainConnection;
        public bool isTransactional;
        public SqlTransaction transaction;
        private String connectionString;

        /// <summary>
        /// constructor for setting transaction
        /// </summary>
        /// <param name="isTransactional">if connection is transactional or not</param>
        public ConnectionManager(bool isTransactional, String connectionString)
        {
            CreateConnection(connectionString);
            this.isTransactional = isTransactional;
        }
        /// <summary>
        /// default constructor
        /// </summary>
        public ConnectionManager(String connectionString)
        {
            CreateConnection(connectionString);
            isTransactional = false;
        }
        /// <summary>
        /// create the connection according to app.config file
        /// </summary>
        private void CreateConnection(String connectionString)
        {
            //string sConnStr = ConfigurationSettings.AppSettings["mainConn"];
            this.connectionString = connectionString;
            mainConnection = new SqlConnection(this.connectionString);
        }
        /// <summary>
        /// opens the connection and if necessary sets the transaction
        /// </summary>
        public void OpenConnection()
        {
            mainConnection.Open();
            if (isTransactional == true)
                transaction = mainConnection.BeginTransaction();
        }
        /// <summary>
        /// closes the connection and if necessary transaction
        /// </summary>
        /// <param name="isSuccess">set false inorder to rollback and true otherwise</param>
        public void CloseConnection(bool isSuccess)
        {
            if (isTransactional == true)
            {
                if (isSuccess == false)
                    transaction.Rollback();
                else
                    transaction.Commit();
            }
            mainConnection.Close();
        }
        /// <summary>
        /// retrieves data according to parameters
        /// </summary>
        /// <param name="sSql">select query</param>
        /// <param name="parameters">select query parameters</param>
        /// <returns></returns>
        public void getData(ref DataTable dtBuffer, string sSql, List<SqlParameter> parameters)
        {
            try
            {
                if (mainConnection.State == ConnectionState.Closed)
                    OpenConnection();
                SqlDataAdapter da = new SqlDataAdapter(sSql, mainConnection);
                da.SelectCommand.Transaction = transaction;
                if (parameters != null)
                    da.SelectCommand.Parameters.AddRange(parameters.ToArray());
                da.Fill(dtBuffer);
                if (!isTransactional)
                    CloseConnection(true);
            }
            catch (Exception ex)
            {
                CloseConnection(false);
                throw ex;
            }

        }
        /// <summary>
        /// retrieves data
        /// </summary>
        /// <param name="sSql">select query</param>
        /// <returns></returns>
        public void getData(DataSet dtBuffer, string sSql)
        {
            try
            {
                //if (mainConnection.State == ConnectionState.Closed)
                //    OpenConnection();
                //SqlDataAdapter da = new SqlDataAdapter(sSql, mainConnection);
                //da.SelectCommand.Transaction = transaction;
                //da.Fill(dtBuffer);
                if (dtBuffer.Tables.Count <= 0)
                    dtBuffer.Tables.Add();
                DataTable dt = dtBuffer.Tables[0];
                getData(ref dt, sSql, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void getData(DataSet dtBuffer, string sSql, List<SqlParameter> parameters)
        {

            //if (mainConnection.State == ConnectionState.Closed)
            //    OpenConnection();
            //SqlDataAdapter da = new SqlDataAdapter(sSql, mainConnection);
            //da.SelectCommand.Transaction = transaction;
            //da.Fill(dtBuffer);
            if (dtBuffer.Tables.Count <= 0)
                dtBuffer.Tables.Add();
            DataTable dt = dtBuffer.Tables[0];
            getData(ref dt, sSql, parameters);

        }

        public void getData(ref DataTable dtBuffer, SqlCommand cmd)
        {
            try
            {
                if (mainConnection.State == ConnectionState.Closed)
                    OpenConnection();

                cmd.Connection = mainConnection;
                cmd.Transaction = transaction;
                //da.SelectCommand.Connection = mainConnection;
                //da.SelectCommand.Transaction = transaction;
                //da.SelectCommand = cmd;
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(dtBuffer);
                if (!isTransactional)
                    CloseConnection(true);
            }
            catch (Exception ex)
            {
                CloseConnection(false);
                throw ex;
            }

        }


        /// <summary>
        /// executes a query
        /// </summary>
        /// <param name="sSQL">sql query to execute</param>
        /// <param name="parameters">sql query parameters</param>
        /// <returns></returns>
        public bool execSQL(string sSQL, List<SqlParameter> parameters)
        {
            try
            {
                if (mainConnection.State == ConnectionState.Closed)
                    OpenConnection();
                SqlCommand cmd = new SqlCommand(sSQL, mainConnection);
                cmd.Transaction = transaction;
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters.ToArray());
                cmd.ExecuteNonQuery();
                if (!isTransactional)
                    CloseConnection(true);
                return true;
            }
            catch (Exception ex)
            {
                CloseConnection(false);
                throw ex;
            }
        }
        public bool execSQL(string sSQL, SqlCommand cmd)
        {
            try
            {
                if (mainConnection.State == ConnectionState.Closed)
                    OpenConnection();

                cmd.Connection = mainConnection;
                cmd.CommandText = sSQL;

                cmd.Transaction = transaction;
                cmd.ExecuteNonQuery();
                if (!isTransactional)
                    CloseConnection(true);
                return true;
            }
            catch (Exception ex)
            {
                CloseConnection(false);
                throw ex;
            }
        }
        /// <summary>
        /// executes a query
        /// </summary>
        /// <param name="sSQL">sql query to execute</param>
        /// <param name="parameters">sql query parameters</param>
        /// <returns></returns>
        public int execSQLRowCount(string sSQL, List<SqlParameter> parameters)
        {
            try
            {
                if (mainConnection.State == ConnectionState.Closed)
                    OpenConnection();
                SqlCommand cmd = new SqlCommand(sSQL, mainConnection);
                cmd.Transaction = transaction;
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters.ToArray());
                if (!isTransactional)
                    CloseConnection(true);
                return cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                CloseConnection(false);
                throw ex;
            }
        }
        /// <summary>
        /// saves a group of data to database
        /// </summary>
        /// <param name="dataSet">dataset that contains new data</param>
        /// <param name="sqlCumlesi">select query</param>
        /// <param name="tabloAdi">table name that contains data</param>
        /// <returns></returns>
        public int TopluVeriIsleme(DataSet dataSet, string sqlCumlesi, string tabloAdi)
        {
            return TopluVeriIsleme(dataSet.Tables[tabloAdi], sqlCumlesi);
        }
        /// <summary>
        /// saves a group of data to database
        /// </summary>
        /// <param name="dataSet">dataset that contains new data</param>
        /// <param name="sqlCumlesi">select quer</param>
        /// <returns></returns>
        public int TopluVeriIsleme(DataSet dataSet, string sqlCumlesi)
        {
            return TopluVeriIsleme(dataSet.Tables[0], sqlCumlesi);
        }
        /// <summary>
        /// saves a group of data to database
        /// </summary>
        /// <param name="dataSet">dataset that contains new data</param>
        /// <returns></returns>
        public int TopluVeriIsleme(DataSet dataSet)
        {
            return TopluVeriIsleme(dataSet.Tables[0], "SELECT * FROM " + dataSet.Tables[0].TableName);
        }
        /// <summary>
        /// saves a group of data to database
        /// </summary>
        /// <param name="dataTable">datatable that contains new data</param>
        /// <returns></returns>
        public int TopluVeriIsleme(DataTable dataTable)
        {
            return TopluVeriIsleme(dataTable, "SELECT * FROM " + dataTable.TableName);
        }
        /// <summary>
        /// saves a group of data to database
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="sqlCumlesi"></param>
        /// <returns></returns>
        public int TopluVeriIsleme(DataTable dataTable, string sqlCumlesi)
        {
            int functionReturnValue = 0;
            try
            {
                if (mainConnection.State == ConnectionState.Closed)
                    OpenConnection();
                using (SqlDataAdapter DataAdapter = new SqlDataAdapter(sqlCumlesi, mainConnection))
                {
                    if (isTransactional) DataAdapter.SelectCommand.Transaction = transaction;
                    using (SqlCommandBuilder CommandBuilder = new SqlCommandBuilder(DataAdapter))
                    {
                        functionReturnValue = DataAdapter.Update(dataTable);
                    }
                }
                if (!isTransactional)
                    CloseConnection(true);
                else
                    CloseConnection(true);
            }
            catch (Exception ex)
            {
                CloseConnection(false);
                throw ex;
            }
            return functionReturnValue;
        }

        /// <summary>
        /// saves a group of data to database and renews the identity columns in the source
        /// </summary>
        /// <param name="ds"></param>
        public void TopluVeriIslemeIDDondur(DataSet ds)
        {
            string sIDFieldName = "";
            foreach (DataColumn dc in ds.Tables[0].Columns)
            {
                if (dc.AutoIncrement == true)
                {
                    sIDFieldName = dc.Caption;
                    break;
                }
            }
            TopluVeriIslemeIDDondur(ds, sIDFieldName);
        }
        /// <summary>
        /// saves a group of data to database and renews the identity columns in the source
        /// </summary>
        /// <param name="dt"></param>
        public void TopluVeriIslemeIDDondur(DataTable dt)
        {
            //string sIDFieldName = dt.TableName + "ID";
            string sIDFieldName = "";
            foreach (DataColumn dc in dt.Columns)
            {
                if (dc.AutoIncrement == true)
                {
                    sIDFieldName = dc.Caption;
                    break;
                }
            }
            TopluVeriIslemeIDDondur(dt, sIDFieldName);
        }
        /// <summary>
        /// saves a group of data to database and renews the identity columns in the source
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="sIDFieldName"></param>
        public void TopluVeriIslemeIDDondur(DataSet ds, string sIDFieldName)
        {
            string ssql = "SELECT * FROM " + ds.Tables[0].TableName;
            DataTable dt = ds.Tables[0];
            TopluVeriIslemeIDDondur(dt, ssql, sIDFieldName);
        }
        /// <summary>
        /// saves a group of data to database and renews the identity columns in the source
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sIDFieldName"></param>
        public void TopluVeriIslemeIDDondur(DataTable dt, string sIDFieldName)
        {
            string ssql = "SELECT * FROM " + dt.TableName;
            TopluVeriIslemeIDDondur(dt, ssql, sIDFieldName);
        }

        /// <summary>
        /// saves a group of data to database and renews the identity columns in the source
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="ssql"></param>
        /// <param name="sIDFieldName"></param>
        /// <returns></returns>
        public int TopluVeriIslemeIDDondur(DataTable dt, string ssql, string sIDFieldName)
        {
            int functionReturnValue = 0;

            if (mainConnection.State == ConnectionState.Closed)
                OpenConnection();
            SqlDataAdapter adp = new SqlDataAdapter(ssql, mainConnection);
            SqlDataAdapter adptmp = new SqlDataAdapter(ssql, mainConnection);

            if (isTransactional)
                adp.SelectCommand.Transaction = transaction;
            try
            {
                SqlCommandBuilder cb = new SqlCommandBuilder(adp);
                SqlCommand cmd;
                cmd = cb.GetInsertCommand();
                cmd.CommandText += "; " + ssql + " Where " + sIDFieldName + "=@@IDENTITY";
                cmd.UpdatedRowSource = UpdateRowSource.Both;
                adptmp.InsertCommand = cmd;
                adptmp.UpdateCommand = cb.GetUpdateCommand();
                adptmp.DeleteCommand = cb.GetDeleteCommand();

                adptmp.Update(dt);
            }
            catch (Exception ex)
            {
                CloseConnection(false);
                throw ex;
            }

            return functionReturnValue;
        }

        public SqlParameter CreateParameter(string name, object value)
        {
            if (name.StartsWith("@") == false)
                name = "@" + name;
            SqlParameter parameter = new SqlParameter(name, value);
            return parameter;
        }

        private void checkForInjection(string sql)
        {
            string[] keys = { "insert ", "update ", "delete ", "drop ", "truncate " };    
            int indexOfLen = 20;
            if (sql.Length < indexOfLen)
            {
                indexOfLen = sql.Length;
            }
            foreach (string key in keys)
            {
                if (sql.IndexOf(key, indexOfLen, sql.Length - indexOfLen, StringComparison.InvariantCultureIgnoreCase) >= 0)
                {
                    string msg = string.Format("SQL Injection\nKey = {0}\nSQL = {1}", key, sql);
                    System.Diagnostics.EventLog.WriteEntry("IKU", msg, System.Diagnostics.EventLogEntryType.Error);
                    throw new Exception(string.Format("SQL Injection\nKey = {0}", key));
                }
            }
        }
    }
}
